﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.IO;

namespace Technoera.Common.Interop.OleDb
{
    public sealed class XlsHelper
    {
        private XlsHelper(){}

        public static string GetExcelConnectionString(string xlsFullFileName)
        {
            switch (Path.GetExtension(xlsFullFileName).ToLower())
            {
                case ".xls":
                    return GetExcelConnectionString(xlsFullFileName, ExcelVersion.V8);
                case ".xlsx":
                    return GetExcelConnectionString(xlsFullFileName, ExcelVersion.V12);
                default:
                    throw new NotSupportedException(string.Format("Unable to recognize {0} extension.", Path.GetExtension(xlsFullFileName).ToLower()));
            }
        }
        
        /// <summary>
        /// Provides the connection string for the specified xls file.
        /// </summary>
        public static string GetExcelConnectionString(string xlsFullFileName, ExcelVersion excelVersion)
        {
            switch (excelVersion)
            {
                case ExcelVersion.V8:
                    return string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES;\"", xlsFullFileName);
                case ExcelVersion.V12:
                    return string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;", xlsFullFileName);
                default:
                    throw new NotSupportedException(excelVersion.ToString());
            }
        }

        //public static IList<string> GetExcelSheetNames(ExcelVersion excelVersion, string xlsFullFileName)
        //{
        //    throw new NotImplementedException("Not implemented: ExcelHelper.GetExcelSheetNames"); //TODO: ExcelHelper.GetExcelSheetNames 
        //}

        public static IList<string> GetExcelColumnNames(ExcelVersion excelVersion, string xlsFullFileName, string sheetName)
        {
            using (OleDbConnection cn = new OleDbConnection(GetExcelConnectionString(xlsFullFileName, excelVersion)))
            {
                using (OleDbDataAdapter da = new OleDbDataAdapter(String.Format("SELECT * FROM [{0}$]", sheetName), cn))
                {
                    try
                    {
                        DataTable table = new DataTable();
                        da.FillSchema(table, SchemaType.Source);
                        List<string> result = new List<string>();
                        foreach (DataColumn item in table.Columns)
                        {
                            result.Add(item.ColumnName);
                        }
                        return result;            
                    }
                    finally
                    {
                        if (cn != null && cn.State == System.Data.ConnectionState.Open)
                        {
                            cn.Close();
                        }
                    }
                }
            }
        }

        public static void DeleteSheet(string xlsFullFileName, ExcelVersion excelVersion, string sheetName)
        {
            using (OleDbConnection cn = new OleDbConnection(GetExcelConnectionString(xlsFullFileName, excelVersion)))
            {
                using (OleDbCommand cmd = new OleDbCommand(string.Format("DROP TABLE [{0}]", sheetName), cn))
                {
                    try
                    {
                        cn.Open();
                        cmd.ExecuteNonQuery();
                    }
                    finally
                    {
                        if (cn.State != ConnectionState.Closed)
                        {
                            try
                            {
                                cn.Close();
                            }
                            catch { }
                        }
                    }
                }
            }
        }
    }

    public enum ExcelVersion { V8, V12 }

}
